﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Xml.Linq;
using System.Data.Linq;
using Settings;
using System.Data.SqlClient;
using System.Data;

namespace Sami_SMS_CRM_Business
{
    public class SubscriberClassificationRepository : Repository
    {
        public IList<crm_subscriber_classification> GetAll()
        {
            return DB.crm_subscriber_classifications.ToList();
        }

        public IList<crm_subscriber_classification> GetAll(string subscriber, DateTime? timeFrom, DateTime? timeTo,
                                            int currentPage, int pageSize, ref int totalRecord)
        {
            var retValue = DB.crm_subscriber_classifications
                            .Where(p =>
                                (string.IsNullOrEmpty(subscriber) ? true : (p.subscriber.Contains(subscriber))) &&
                                (timeFrom == null ? true : (p.received_time >= timeFrom)) &&
                                (timeTo == null ? true : (p.received_time <= timeTo))
                            )
                            .OrderByDescending(p => p.seq_id);
            totalRecord = retValue.Count();
            return retValue.Skip((currentPage - 1) * pageSize).Take(pageSize).ToList();
        }

        public IList<crm_subscriber_classification> GetByCollection(crm_collection colection)
        {
            if (colection == null || colection.collection_value == null)
                return null;
            return DB.ExecuteQuery<crm_subscriber_classification>(GetQuery(colection)).ToList();
        }

        public IList<crm_subscriber_classification> GetByAdvExpired()
        {
            List<crm_subscriber_classification> retValue = new List<crm_subscriber_classification>();
            var collections = DB.crm_collections
                            .Where(cc => DB.crm_advertisments
                                    .Where(ca => ca.end_expected < DateTime.Now && ca.action_id == (int)ActionId.WhenMessageReceived)
                                    .Select(ca => ca.collection_id).Contains(cc.collection_id));
            if (collections == null) return null;
            foreach (var item in collections)
            {
                var cols = GetByCollection(item);
                if (cols != null && cols.Count > 0)
                    retValue.AddRange(cols);
            }
            return retValue.Distinct(new SubscriberClassificationCompare()).ToList();
        }

        public IList<crm_subscriber_classification> GetByAdvExpired(int currentPage, int pageSize, ref int totalRecord)
        {
            var retValue = GetByAdvExpired();
            if (retValue == null || retValue.Count == 0)
            {
                totalRecord = 0;
                return null;
            }
            totalRecord = retValue.Count;
            return retValue.Skip((currentPage - 1) * pageSize).Take(pageSize).ToList();
        }

        public IList<crm_subscriber_classification> GetByAdvUnExpired()
        {
            List<crm_subscriber_classification> retValue = new List<crm_subscriber_classification>();
            var collections = DB.crm_collections
                            .Where(cc => DB.crm_advertisments
                                    .Where(ca => ca.end_expected >= DateTime.Now && ca.action_id == (int)ActionId.WhenMessageReceived)
                                    .Select(ca => ca.collection_id).Contains(cc.collection_id));
            if (collections == null) return null;
            foreach (var item in collections)
            {
                var cols = GetByCollection(item);
                if (cols != null && cols.Count > 0)
                    retValue.AddRange(cols);
            }
            return retValue.Distinct(new SubscriberClassificationCompare()).ToList();
        }

        public IList<crm_subscriber_classification> GetByAdvUnExpired(int currentPage, int pageSize, ref int totalRecord)
        {
            var retValue = GetByAdvUnExpired();
            if (retValue == null || retValue.Count == 0)
            {
                totalRecord = 0;
                return null;
            }
            totalRecord = retValue.Count;
            return retValue.Skip((currentPage - 1) * pageSize).Take(pageSize).ToList();
        }

        public crm_subscriber_classification GetById(Guid Id)
        {
            return DB.crm_subscriber_classifications.FirstOrDefault(p => p.guid_key == Id);
        }

        public void DeleteById(Guid Id)
        {
            var entity = DB.crm_subscriber_classifications.FirstOrDefault(p => p.guid_key == Id);
            if (entity != null)
            {
                DB.crm_subscriber_classifications.DeleteOnSubmit(entity);
                DB.SubmitChanges();
            }
        }

        public int DeleteRecordExpired()
        {
            DataTable table = new DataTable();
            table.Columns.Add("guid_key", typeof(Guid));
            var expiredList = GetByAdvExpired();
            if (expiredList == null || expiredList.Count == 0) return 0;
            foreach (var item in expiredList)
            {
                table.Rows.Add(item.guid_key);
            }
            using (SqlConnection conn = new SqlConnection(LocalSettings.ConnectionString))
            {
                conn.Open();
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.CommandText = "sp_crm_subscriber_classification_Delete";
                    cmd.Parameters.AddWithValue("@GuidKey", table);
                    cmd.Parameters["@GuidKey"].SqlDbType = SqlDbType.Structured;
                    cmd.Parameters["@GuidKey"].TypeName = "dbo.GuidKey_type";
                    cmd.ExecuteNonQuery();
                    return 1;
                }
            }
        }

        public string GetQuery(crm_collection entity)
        {
            XElement element = entity.collection_value;
            StringBuilder retValue = new StringBuilder("SELECT * ");
            string fromClause = element.Element("from").Value;
            string whereClause = element.Element("where").Value;
            retValue.AppendLine("FROM " + fromClause);
            if (!string.IsNullOrEmpty(whereClause))
                retValue.AppendLine("WHERE " + whereClause);
            return retValue.ToString();
        }
    }
}
